--EXEC SP_GET_WAFERS_FOR_DIE_BANK_BINNING_INFO 4746, 4

ALTER PROC sp_get_wafers_for_die_bank_binning_info
@LOT_ID INT,
@GOOD_BINS_COUNT INT
AS
BEGIN
	DECLARE @WAFER_ID INT
	DECLARE @IS_EDITABLE BIT
	DECLARE @BIN_QUANTITIES VARCHAR(1000)
	DECLARE @RET_BIN_QUANTITIES VARCHAR(1000)
	DECLARE @BIN_REJECTED INT
	DECLARE @YIELD FLOAT
	DECLARE @QUERY_STR NVARCHAR(1000)
	DECLARE @PARAM_STR NVARCHAR(500)
	DECLARE @MRB_LIMIT BIT
	DECLARE @RMA_LIMIT BIT
	DECLARE @IS_HOLD BIT
	DECLARE @IDX INT

	DECLARE @ARLT_BIN_INFO VARCHAR(200)
	DECLARE @BIN_INFO VARCHAR(200)	
	DECLARE @BIN_INFO_COPY VARCHAR(200)
	DECLARE @ARLT_BIN_QTY INT
	DECLARE @ARLT_TOTAL_BIN_QTY BIGINT
	DECLARE @ARLT_CURSOR CURSOR
	DECLARE @SP_CHAR_IDX INT
	DECLARE @ARLT_BIN VARCHAR(10)
	DECLARE @SP_CHAR_IDX_FOR_BIN_INFO INT
	DECLARE @BIN_INFO_BIN_COUNT INT
	DECLARE @IB_GOOD_DICE BIGINT
	DECLARE @ART_STATUS VARCHAR(50)

	DECLARE @RESULT_TABLE TABLE( WAFER_ID INT, RET_BINS_QUANTITIES VARCHAR(200), BIN_REJECTED INT, YIELD FLOAT, IS_EDITABLE BIT, IS_HOLD BIT, BINS_USED_FOR_ASSEMBLY_RELEASE VARCHAR(200) )
	SET @BIN_QUANTITIES = ''
	SET @IDX = 1
	WHILE( @IDX <= @GOOD_BINS_COUNT  )
	BEGIN
		SET @BIN_QUANTITIES = @BIN_QUANTITIES + 'CONVERT( VARCHAR, ISNULL(BIN_'+ CONVERT(VARCHAR, @IDX) +',0))+'';''+'
		SET @IDX = @IDX+1
	END
	SET @BIN_QUANTITIES = SUBSTRING(@BIN_QUANTITIES,0,LEN(@BIN_QUANTITIES))	
	SET @QUERY_STR = 'DECLARE WAFERS_CURSOR CURSOR FOR SELECT WAFER_ID, ' + @BIN_QUANTITIES + ' AS BINS_QTY,BIN_REJECTED, YIELD, MRB_LIMIT, RMA_LIMIT  FROM INFORMATIVE_BINNING WHERE LOT_ID = @LOT_ID ORDER BY cast(WAFER_ID as int)'
	SET @PARAM_STR = '@LOT_ID INT'
	EXECUTE SP_EXECUTESQL @QUERY_STR, @PARAM_STR, @LOT_ID
	OPEN WAFERS_CURSOR
	FETCH FROM WAFERS_CURSOR INTO @WAFER_ID, @RET_BIN_QUANTITIES, @BIN_REJECTED, @YIELD, @MRB_LIMIT, @RMA_LIMIT 
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @IS_EDITABLE = 0
		SET @BIN_INFO = ''
		/*
		If a wafer is present in engineering lot table (ie., the wafer is either completely/partially scrapped) 
		The wafer is disabled.
		If a wafer is partially released to assembly and if any one of the assembly releases generated for that 
		wafer has 'issued' or 'approved' status it is disabled, (such wafers are not eligible for partial scrapping)

		If a wafer is partially released to Assembly and and all the Assembly releases generated for that wafer
		are in 'closed' or 'shipped' status, the wafer is enabled. ie., the wafer can be partially scrapped. 
		In this case 'BINS_USED_FOR_ASSEMBLY_RELEASE' column of @RESULTS_TABLE will have all the bins of the wafer
		that are released to Assembly process, and the wafer is enabled.
		*/
		IF( ((SELECT COUNT(*) FROM ENGINEERING_LOTS  WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID) > 0) OR
			((SELECT COUNT(*) FROM ASSM_RELEASE_LOT_TABLE ASLT, ASSM_RELEASE_TABLE ART WHERE ART.ASSEMBLY_RELEASE_ID = ASLT.ASSEMBLY_RELEASE_ID AND ART.STATUS IN ('ISSUED','APPROVED') AND ASLT.LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID) > 0) )
		BEGIN
			SET @IS_EDITABLE = 1
		END
		ELSE IF( (SELECT COUNT(*) FROM ASSM_RELEASE_LOT_TABLE ASLT, ASSM_RELEASE_TABLE ART WHERE ART.ASSEMBLY_RELEASE_ID = ASLT.ASSEMBLY_RELEASE_ID AND ART.STATUS <> 'CANCELLED' AND ASLT.LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID) > 0) 
		BEGIN
			--Construct a string having all the bins released to Assembly delimited by '&' char.
		    	SET @BIN_INFO = '&'
			SET @ARLT_TOTAL_BIN_QTY = 0
			SET @ARLT_CURSOR = CURSOR FOR SELECT REPLACE(UPPER(ASLT.BIN_INFO), 'BIN',''), QTY_PULLED FROM ASSM_RELEASE_LOT_TABLE ASLT, ASSM_RELEASE_TABLE ART WHERE ART.ASSEMBLY_RELEASE_ID = ASLT.ASSEMBLY_RELEASE_ID AND ART.STATUS <> 'CANCELLED' AND ASLT.LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID
			OPEN @ARLT_CURSOR
			FETCH FROM @ARLT_CURSOR INTO @ARLT_BIN_INFO, @ARLT_BIN_QTY
			WHILE( @@FETCH_STATUS = 0 )
			BEGIN
				SET @ARLT_BIN_INFO = @ARLT_BIN_INFO + '&'
				SET @SP_CHAR_IDX = CHARINDEX( '&', @ARLT_BIN_INFO )
				WHILE( @SP_CHAR_IDX <> 0 )
				BEGIN
					SET @ARLT_BIN = SUBSTRING( @ARLT_BIN_INFO, 1, @SP_CHAR_IDX )
					SET @SP_CHAR_IDX_FOR_BIN_INFO = CHARINDEX( '&'+@ARLT_BIN, @BIN_INFO)						
					IF( @SP_CHAR_IDX_FOR_BIN_INFO = 0 ) 
					BEGIN
						SET @BIN_INFO = @BIN_INFO + @ARLT_BIN
					END
					SET @ARLT_BIN_INFO = SUBSTRING( @ARLT_BIN_INFO, @SP_CHAR_IDX+1, LEN(@ARLT_BIN_INFO) )
					SET @SP_CHAR_IDX = CHARINDEX( '&', @ARLT_BIN_INFO )						
				END
				SET @ARLT_TOTAL_BIN_QTY = @ARLT_TOTAL_BIN_QTY + @ARLT_BIN_QTY
				FETCH FROM @ARLT_CURSOR INTO @ARLT_BIN_INFO,@ARLT_BIN_QTY
			END
			SET @BIN_INFO = SUBSTRING( @BIN_INFO, 2, LEN(@BIN_INFO) )
			SET @BIN_INFO_COPY = @BIN_INFO
			SET @BIN_INFO_BIN_COUNT = 0
			WHILE( CHARINDEX( '&', @BIN_INFO_COPY ) <> 0 )
			BEGIN
				SET @BIN_INFO_BIN_COUNT = @BIN_INFO_BIN_COUNT + 1
				SET @BIN_INFO_COPY = SUBSTRING( @BIN_INFO_COPY, CHARINDEX('&', @BIN_INFO_COPY )+1, LEN(@BIN_INFO_COPY))
			END
			SELECT @IB_GOOD_DICE = GOOD_DICE FROM INFORMATIVE_BINNING WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID
			--Disable the wafer if all the bins are released to Assembly or if enire good dice is used for Assembly.
			IF( (@BIN_INFO_BIN_COUNT >= @GOOD_BINS_COUNT) or (@ARLT_TOTAL_BIN_QTY=@IB_GOOD_DICE) )
			BEGIN
				SET @IS_EDITABLE = 1
			END
		END
			
		SET @IS_HOLD = 1	
		IF ( @MRB_LIMIT = 0 AND @RMA_LIMIT = 1 )
		BEGIN
			SET @IS_HOLD = 0	-- 0 means Wafer is on HOLD i.e. below the MRB Limit.
		END

		SET @RET_BIN_QUANTITIES = SUBSTRING( @RET_BIN_QUANTITIES, 0, LEN(@RET_BIN_QUANTITIES))
		INSERT INTO @RESULT_TABLE ( WAFER_ID, RET_BINS_QUANTITIES, BIN_REJECTED, YIELD, IS_EDITABLE, IS_HOLD, BINS_USED_FOR_ASSEMBLY_RELEASE ) VALUES ( @WAFER_ID, @RET_BIN_QUANTITIES, @BIN_REJECTED, @YIELD, @IS_EDITABLE, @IS_HOLD, @BIN_INFO )
		FETCH FROM WAFERS_CURSOR INTO @WAFER_ID, @RET_BIN_QUANTITIES, @BIN_REJECTED, @YIELD, @MRB_LIMIT, @RMA_LIMIT
	END
	CLOSE WAFERS_CURSOR
	DEALLOCATE WAFERS_CURSOR
	SELECT * FROM @RESULT_TABLE
END